package com.project.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;

import com.project.common.Constants;
import com.project.dao.impl.BaseDaoImpl;
import com.project.webapp.form.BaseForm;
import com.project.webapp.form.front.preSchool.ArticleForm;

/**
 * 一些公共的标签
 * 
 * @author HLJ
 * 
 */
public class TagDaoImpl extends BaseDaoImpl implements IDaoTag {

	@Override
	public List<BaseForm> getNewsTitle(ArticleForm artForm, Connection con,
			Log log) throws Exception {
		List<BaseForm> list = new ArrayList<BaseForm>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.article_id,a.article_title,a.article_content,a.pic_link,m.menu_name,m.MENU_SORT ")
				.append("FROM   (SELECT article_id,article_title,menu_id,article_content,pic_link ")
				.append("        FROM   article ")
				.append("        WHERE  menu_id IN (SELECT descendant_id ")
				.append("                          FROM   menu_relation ")
				.append("                           WHERE  ancestor_id = (SELECT menu_id ")
				.append("                                                 FROM   menu ")
				.append("                                                 WHERE  menu_seq = 4 ")
				.append("                                                        AND show_sort = ? ")
				.append("                                                        AND menu_id IN (SELECT descendant_id ")
				.append("                                                                        FROM   menu_relation ")
				.append("                                                                        WHERE  ancestor_id IN (SELECT menu_id ")
				.append("                                                                                               FROM   menu ")
				.append("                                                                                               WHERE  menu_code IN (")
				.append(Constants.MENU_COURSE)
				.append(")) ")
				.append("       AND ancestor_id != descendant_id) limit 1)) limit ? ) a ")
				.append("       LEFT JOIN menu m ")
				.append("        ON a.menu_id = m.menu_id ");
		PreparedStatement ps = con.prepareStatement(sql.toString());
		ps.setString(1, artForm.getCount());
		ps.setInt(2, Integer.parseInt(artForm.getRecordCount()));
		ResultSet rs = ps.executeQuery();
		if(artForm.getCount().equals("1")){
			System.out.println("sql语句："+sql.toString());
			
		}
		ArticleForm art = null;
		while (rs.next()) {
			art = new ArticleForm();
			art.setArticle_id(rs.getString("article_id"));
			art.setArticle_title(rs.getString("article_title"));
			art.setArticle_content(rs.getString("article_content"));
			art.setPic_link(rs.getString("pic_link"));
			art.setMenu_name(rs.getString("menu_name"));
			art.setArticleseq(rs.getInt("MENU_SORT"));
			//System.out.println("打印出对应的菜单的层次："+rs.getInt("MENU_SORT"));
			list.add(art);
		}
		
		rs.close();
		
		return list;
		
	}

	@Override
	public List<BaseForm> getNewsCourseTitle(ArticleForm artForm,
			Connection con, Log log) throws Exception {
		//改良版方法，先查处menu_code，然后在截取menu_code，去模糊匹配对应的子菜单
		//查找出对应子菜单下面的菜单分类下面的课程
		List<BaseForm> list = new ArrayList<BaseForm>();
		//定义查找出来的menu_code
		String menu_code=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select menu_code from menu where show_sort=? ";
		ps=con.prepareStatement(sql);
		ps.setString(1, artForm.getCount());
		rs=ps.executeQuery();
		rs.next();
		//截取菜单menu_code
		menu_code=rs.getString("menu_code").substring(0, 8);
		//截取之后的菜单名称
		sql="select a.article_id,a.article_title,a.area,m.menu_name,m.menu_id FROM article a LEFT JOIN menu m ON (a.menu_id=m.MENU_ID) WHERE m.MENU_CODE LIKE ? LIMIT 0,?; ";
		ps=con.prepareStatement(sql);
		ps.setString(1, menu_code+"%");
		ps.setInt(2, Integer.parseInt(artForm.getRecordCount()));
		rs=ps.executeQuery();
		ArticleForm art=null;
		while(rs.next()){
			art=new ArticleForm();
			//需要读取数据库的字段
			//所属菜单名称,标题,地区,课时，收费，新闻标题id，菜单id
			art.setMenuId(rs.getString("menu_id"));//菜单id
			art.setMenu_name(rs.getString("menu_name"));//菜单名称
			art.setArticle_id(rs.getString("article_id"));//标题id
			art.setArticle_title(rs.getString("article_title"));//标题名称
			art.setArea(rs.getString("area"));//地区
			//art.setCourse_fee(rs.getString("course_fee"));//收费
			//art.setCourse_hour(rs.getInt("course_hour"));//上课时间
			list.add(art);
			
		}
		if(list.size()<8){
			sql="select menu_name from menu where show_sort=? ";
			ps=con.prepareStatement(sql);
			ps.setString(1, artForm.getCount());
			rs=ps.executeQuery();
			rs.next();
			String menu_name=rs.getString("menu_name");
			artForm.setMenu_name(menu_name);
		}
		//取出要像是菜单的menu_code
//		String sql1="select menu_code from menu whrere show_sort=?";
//		PreparedStatement ps1=con.prepareStatement(sql1);
//		ps1.setInt(1, Integer.parseInt(artForm.getRecordCount()));
//		ResultSet rs1=null;
//		rs1=ps1.executeQuery(sql1);
//		rs1.next();
//		String menu_code=rs1.getString("menu_code");
//		System.out.println("菜单menu_code："+menu_code);
		
//		StringBuffer sql = new StringBuffer();
//		//menu_sort菜单是否显示
//		sql.append("SELECT a.article_id,a.article_title,area,course_hour,course_fee,m.menu_name,a.menu_id,m.menu_sort ")
//		.append("FROM   (SELECT article_id,article_title,area,course_hour,course_fee,menu_id")
//		.append("        FROM   article ")
//		.append("        WHERE  menu_id IN (SELECT descendant_id ")
//		.append("                          FROM   menu_relation ")
//		.append("                           WHERE  ancestor_id = (SELECT menu_id ")
//		.append("                                                 FROM   menu ")
//		.append("                                                 WHERE  menu_seq = 4 ")
//		.append("                                                        AND show_sort = ? ")
//		.append("                                                        AND menu_id IN (SELECT descendant_id ")
//		.append("                                                                        FROM   menu_relation ")
//		.append("                                                                        WHERE  ancestor_id IN (SELECT menu_id ")
//		.append("                                                                                               FROM   menu ")
//		.append("                                                                                               WHERE  menu_code IN (")
//		.append(Constants.MENU_COURSE)
//		.append(")) ")
//		.append("       AND ancestor_id != descendant_id) limit 1)) limit ? ) a ")
//		.append("       LEFT JOIN menu m ")
//		.append("        ON a.menu_id = m.menu_id ");
//		PreparedStatement ps=con.prepareStatement(sql);
//		ps.setString(1, artForm.getCount());
//		ps.setInt(2, Integer.parseInt(artForm.getRecordCount()));
//		ResultSet rs=ps.executeQuery();
//		ArticleForm art=null;
//		while(rs.next()){
//			art=new ArticleForm();
//			//需要读取数据库的字段
//			//所属菜单名称,标题,地区,课时，收费，新闻标题id，菜单id
//			art.setMenuId(rs.getString("menu_id"));//菜单id
//			art.setMenu_name(rs.getString("menu_name"));//菜单名称
//			art.setArticle_id(rs.getString("article_id"));//标题id
//			art.setArticle_title(rs.getString("article_title"));//标题名称
//			art.setArea(rs.getString("area"));//地区
//			art.setCourse_fee(rs.getString("course_fee"));//收费
//			art.setCourse_hour(rs.getInt("course_hour"));//上课时间
//			list.add(art);
//		}
		if(list.size()<8){
			for(int i=list.size();i<8;i++){
				list.add(artForm);
			}
		}
		
		rs.close();
		//System.out.println("list:"+list.size());
		return list;
	}

}
